--    Program: 		CAPPOIMP_REQUISITION_DISTRIBUTION.sql
--    Description:  Import PO Req Distributions All information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Kenny Leung
--    Date:         23-Jan-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp			varchar2(2000);

   l_distribution_id        number;
                    		                 		
   cursor cur_req_distributions_all is
      select * from CRST_PO_REQ_DISTRIBUTIONS_ALL for update;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_req_distributions_all loop   	
     l_err_msg_tmp := '';
     
     select PO_REQ_DISTRIBUTIONS_S.NEXTVAL 
     into l_distribution_id
     FROM DUAL;
     
     update CRST_PO_REQ_DISTRIBUTIONS_ALL
     set    distribution_id_n = l_distribution_id
     where  distribution_id = cur.distribution_id;
     
     begin
       -- import data
       insert into PO_REQ_DISTRIBUTIONS_ALL 
       (
            DISTRIBUTION_ID              ,
            LAST_UPDATE_DATE             ,
            LAST_UPDATED_BY              ,
            REQUISITION_LINE_ID          ,
            SET_OF_BOOKS_ID              ,
            CODE_COMBINATION_ID          ,
            REQ_LINE_QUANTITY            ,
            LAST_UPDATE_LOGIN            ,
            CREATION_DATE                ,
            CREATED_BY                   ,
            ENCUMBERED_FLAG              ,
            GL_ENCUMBERED_DATE           ,
            GL_ENCUMBERED_PERIOD_NAME    ,
            GL_CANCELLED_DATE            ,
            FAILED_FUNDS_LOOKUP_CODE     ,
            ENCUMBERED_AMOUNT            ,
            BUDGET_ACCOUNT_ID            ,
            ACCRUAL_ACCOUNT_ID           ,
            VARIANCE_ACCOUNT_ID          ,
            PREVENT_ENCUMBRANCE_FLAG     ,
            ATTRIBUTE_CATEGORY           ,
            ATTRIBUTE1                   ,
            ATTRIBUTE2                   ,
            ATTRIBUTE3                   ,
            ATTRIBUTE4                   ,
            ATTRIBUTE5                   ,
            ATTRIBUTE6                   ,
            ATTRIBUTE7                   ,
            ATTRIBUTE8                   ,
            ATTRIBUTE9                   ,
            ATTRIBUTE10                  ,
            ATTRIBUTE11                  ,
            ATTRIBUTE12                  ,
            ATTRIBUTE13                  ,
            ATTRIBUTE14                  ,
            ATTRIBUTE15                  ,
            USSGL_TRANSACTION_CODE       ,
            GOVERNMENT_CONTEXT           ,
            REQUEST_ID                   ,
            PROGRAM_APPLICATION_ID       ,
            PROGRAM_ID                   ,
            PROGRAM_UPDATE_DATE          ,
            PROJECT_ID                   ,
            TASK_ID                      ,
            END_ITEM_UNIT_NUMBER         ,
            EXPENDITURE_TYPE             ,
            PROJECT_ACCOUNTING_CONTEXT   ,
            EXPENDITURE_ORGANIZATION_ID  ,
            GL_CLOSED_DATE               ,
            SOURCE_REQ_DISTRIBUTION_ID   ,
            DISTRIBUTION_NUM             ,
            ALLOCATION_TYPE              ,
            ALLOCATION_VALUE             ,
            PROJECT_RELATED_FLAG         ,
            EXPENDITURE_ITEM_DATE        ,
            ORG_ID                       ,
            AWARD_ID                     ,
            RECOVERABLE_TAX              ,
            NONRECOVERABLE_TAX           ,
            RECOVERY_RATE                ,
            TAX_RECOVERY_OVERRIDE_FLAG   ,
            OKE_CONTRACT_LINE_ID         ,
            OKE_CONTRACT_DELIVERABLE_ID  
       )
       values
       (
            l_distribution_id                ,
            cur.LAST_UPDATE_DATE             ,
            CUR.LAST_UPDATED_BY,
            CAPPOIMP_MAPPING.GetPRLineIDMapping(cur.REQUISITION_LINE_ID),
            CAPBRIMP_MAPPING.GetSOBIDMapping(cur.SET_OF_BOOKS_ID),
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.CODE_COMBINATION_ID), --No Such method found inside the package
            cur.REQ_LINE_QUANTITY            ,
            cur.LAST_UPDATE_LOGIN            ,
            cur.CREATION_DATE                ,
            CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
            cur.ENCUMBERED_FLAG              ,
            cur.GL_ENCUMBERED_DATE           ,
            cur.GL_ENCUMBERED_PERIOD_NAME    ,
            cur.GL_CANCELLED_DATE            ,
            cur.FAILED_FUNDS_LOOKUP_CODE     ,
            cur.ENCUMBERED_AMOUNT            ,
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.BUDGET_ACCOUNT_ID),
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.ACCRUAL_ACCOUNT_ID),
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.VARIANCE_ACCOUNT_ID),
            cur.PREVENT_ENCUMBRANCE_FLAG     ,
            cur.ATTRIBUTE_CATEGORY           ,
            cur.ATTRIBUTE1                   ,
            cur.ATTRIBUTE2                   ,
            cur.ATTRIBUTE3                   ,
            cur.ATTRIBUTE4                   ,
            cur.ATTRIBUTE5                   ,
            cur.ATTRIBUTE6                   ,
            cur.ATTRIBUTE7                   ,
            cur.ATTRIBUTE8                   ,
            cur.ATTRIBUTE9                   ,
            cur.ATTRIBUTE10                  ,
            cur.ATTRIBUTE11                  ,
            cur.ATTRIBUTE12                  ,
            cur.ATTRIBUTE13                  ,
            cur.ATTRIBUTE14                  ,
            cur.ATTRIBUTE15                  ,
            cur.USSGL_TRANSACTION_CODE       ,
            cur.GOVERNMENT_CONTEXT           ,
            cur.REQUEST_ID                   ,
            cur.PROGRAM_APPLICATION_ID       ,
            cur.PROGRAM_ID                   ,
            cur.PROGRAM_UPDATE_DATE          ,
            cur.PROJECT_ID                   , --NULL
            cur.TASK_ID                      , --NULL
            cur.END_ITEM_UNIT_NUMBER         ,
            cur.EXPENDITURE_TYPE             ,
            cur.PROJECT_ACCOUNTING_CONTEXT   ,
            CAPBRIMP_MAPPING.GetOrganizationMapping(cur.EXPENDITURE_ORGANIZATION_ID),
            cur.GL_CLOSED_DATE               ,
            cur.SOURCE_REQ_DISTRIBUTION_ID   , --NULL
            cur.DISTRIBUTION_NUM             ,
            cur.ALLOCATION_TYPE              ,
            cur.ALLOCATION_VALUE             ,
            cur.PROJECT_RELATED_FLAG         ,
            cur.EXPENDITURE_ITEM_DATE        ,
            CAPBRIMP_MAPPING.GetOrgMapping(cur.ORG_ID),
            cur.AWARD_ID                     ,
            cur.RECOVERABLE_TAX              ,
            cur.NONRECOVERABLE_TAX           ,
            cur.RECOVERY_RATE                ,
            cur.TAX_RECOVERY_OVERRIDE_FLAG   ,
            cur.OKE_CONTRACT_LINE_ID         ,
            cur.OKE_CONTRACT_DELIVERABLE_ID  
       );    
	 exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp IS NULL then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;
   end loop;

   if l_rejected_count = 0 then
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPPOIMP_REQUISITION_DISTRIBUTION', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
